from pyecharts.charts import Bar
from pyecharts import options as opts
from sc.ZDao import getConnect
import os
import sys
from datetime import datetime


def generate_job_count_chart():
    # 强制设置UTF-8编码
    sys.stdout.reconfigure(encoding='utf-8')
    print("=== 开始执行图表生成 ===")

    # 1. 数据获取 ==============================================
    print("\n🔍 正在获取数据...")
    db = None
    try:
        db = getConnect()
        if not db:
            raise Exception("数据库连接失败")

        # 测试查询（先检查表是否存在）
        test_sql = "SELECT 1 FROM jobs LIMIT 1"
        with db.cursor() as cur:
            try:
                cur.execute(test_sql)
                print("✅ 测试查询成功，jobs表存在")
            except Exception as e:
                raise Exception(f"jobs表查询失败，请检查表是否存在: {str(e)}")

        # 正式查询
        sql = """
        SELECT 
            CASE 
                WHEN job_loc LIKE '%·%' THEN SUBSTRING_INDEX(job_loc, '·', 1)
                ELSE job_loc
            END AS city,
            COUNT(*) AS job_count
        FROM jobs
        GROUP BY city
        HAVING COUNT(*) > 0  -- 排除空数据
        ORDER BY job_count DESC
        LIMIT 20
        """

        with db.cursor() as cur:
            cur.execute(sql)
            results = cur.fetchall()

            if not results:
                raise Exception("查询结果为空，请检查jobs表数据")

            cities = []
            counts = []
            for row in results:
                city = row['city'] if isinstance(row, dict) else row[0]
                count = int(row['job_count'] if isinstance(row, dict) else row[1])
                cities.append(city)
                counts.append(count)

            print(f"✅ 获取到{len(cities)}个城市的数据")
            print("样例数据:", list(zip(cities[:3], counts[:3])))

    except Exception as e:
        print(f"❌ 数据获取失败: {str(e)}")
        # 创建空文件用于诊断
        with open("error_log.txt", "w", encoding="utf-8") as f:
            f.write(f"Error: {str(e)}\n")
        return
    finally:
        if db:
            db.close()
            print("🔒 数据库连接已关闭")

    # 2. 图表生成 ==============================================
    print("\n🎨 正在生成图表...")
    try:
        # 创建基础图表
        bar = Bar()
        bar.add_xaxis(cities)
        bar.add_yaxis("岗位数量", counts)

        # 最小化配置测试
        bar.set_global_opts(
            title_opts=opts.TitleOpts(title="各城市岗位数量"),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),
            yaxis_opts=opts.AxisOpts(name="数量")
        )
        print("成功生成图表")



    except Exception as e:
        print(f"❌ 图表生成失败: {str(e)}")
        with open("chart_error.log", "w", encoding="utf-8") as f:
            f.write(f"Chart Error: {str(e)}\n")


if __name__ == "__main__":
    generate_job_count_chart()
    print("\n=== 执行结束 ===")